package com.py.credit.service.excel.read.service.impl;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import com.py.credit.entity.Column;
import com.py.credit.entity.Table;
import com.py.credit.service.excel.read.service.ExcelReadService;
import com.py.credit.utils.DateUtil;

@Service("excelUtilService")
public class ExcelReadServiceImpl implements ExcelReadService {

	private static final Pattern TIME_FORMAT_1 = Pattern
			.compile("^[1-9]\\d{3}/(0[1-9]|1[0-2])/(0[1-9]|[1-2][0-9]|3[0-1])$");
	private static final Pattern TIME_FORMAT_2 = Pattern
			.compile("^[1-9]\\d{3}/([1-9]|1[0-2])/(0[1-9]|[1-2][0-9]|3[0-1])$");
	private static final Pattern TIME_FORMAT_3 = Pattern
			.compile("^[1-9]\\d{3}/(0[1-9]|1[0-2])/([1-9]|[1-2][0-9]|3[0-1])$");
	private static final Pattern TIME_FORMAT_4 = Pattern
			.compile("^[1-9]\\d{3}/([1-9]|1[0-2])/([1-9]|[1-2][0-9]|3[0-1])$");

	private static final Logger LOG = LoggerFactory.getLogger(ExcelReadServiceImpl.class);

	@Override
	public Sheet readExcelFileForSheet(String filePath) throws IOException {
		Workbook wb = null;
		FileInputStream fs = new FileInputStream(filePath);
		// Biff8EncryptionKey.setCurrentUserPassword("AAABBBABABA{");

		if (filePath.endsWith(".xls")) {
			wb = new HSSFWorkbook(fs);
		}
		if (filePath.endsWith(".xlsx")) {
			wb = new XSSFWorkbook(fs);
		}

		Sheet st = wb.getSheetAt(0);

		fs.close();

		return st;
	}

	@Override
	public Sheet readExcelFileForSheet(InputStream inputStream) throws IOException {
		Workbook wb = new XSSFWorkbook(inputStream);
		Sheet st = wb.getSheetAt(0);
		wb.close();
		return st;
	}

	@Override
	public List<Map<String, Object>> readExcelData(int start, int end, Sheet st) {
		// 第一,二行为标题，不取
		Row row = null;
		int length = 0;
		Map<String, Object> map = null;
		Cell cell = null;
		String value = null;

		String[] cloumn = new String[] { "name", "level" };
		length = st.getLastRowNum();

		if (end > length) {
			end = length;
		}
		List<Map<String, Object>> listDataMap = new LinkedList<>();
		for (int rowIndex = start + 1; rowIndex <= end; rowIndex++) {
			row = st.getRow(rowIndex);
			if (row == null) {
				continue;
			}

			short lastCellNum = row.getLastCellNum();

			boolean emptyRow = true;

			for (int cellIndex = 0; cellIndex <= lastCellNum; cellIndex++) {

				if (row.getCell(cellIndex, Row.RETURN_BLANK_AS_NULL) != null) {

					row.getCell(cellIndex).setCellType(HSSFCell.CELL_TYPE_STRING);

					if (!"".equals(row.getCell(cellIndex).getStringCellValue().trim())) {
						{

							emptyRow = false;
							break;
						}
					}

				}
			}

			if (emptyRow) {

				continue;
			}
			map = new HashMap<String, Object>();
			for (int i = 0; i < lastCellNum; i++) {
				value = "";
				cell = row.getCell(i);
				if (cell != null) {
					// 注意：一定要设成这个，否则可能会出现乱码
					switch (cell.getCellType()) {
					case HSSFCell.CELL_TYPE_STRING:
						value = cell.getStringCellValue().trim();
						break;
					case HSSFCell.CELL_TYPE_BLANK:
						break;

					default:
						value = "";
					}
				}
				map.put(cloumn[i], value);
				map.put("rowIndex", rowIndex + 1);
			}
			/* 把数据添加到List里面 */
			listDataMap.add(map);
			LOG.debug(map.toString());
		}

		return listDataMap;
	}

	@Override
	public List<Map<String, Object>> readExcelData(Table table,int start, int end, Sheet st, String batchId, List<Column> columns)
			throws IOException {

		// 第一,二行为标题，不取
		Row row = null;
		int length = 0;
		Map<String, Object> map = null;
		Cell cell = null;
		String value = null;
		Date date = null;

		length = st.getLastRowNum();

		if (end > length) {
			end = length;
		}

		List<Map<String, Object>> listDataMap = new LinkedList<>();

		for (int rowIndex = start; rowIndex <= end; rowIndex++) {
			row = st.getRow(rowIndex);
			if (row == null) {
				continue;
			}

			short lastCellNum = row.getLastCellNum();

			boolean emptyRow = true;

			for (int cellIndex = 0; cellIndex <= lastCellNum; cellIndex++) {

				if (row.getCell(cellIndex, Row.RETURN_BLANK_AS_NULL) != null) {

					row.getCell(cellIndex).setCellType(HSSFCell.CELL_TYPE_STRING);

					if (!"".equals(row.getCell(cellIndex).getStringCellValue().trim())) {
						{

							emptyRow = false;
							break;
						}
					}

				}
			}

			if (emptyRow) {

				continue;
			}
			map = new HashMap<String, Object>();
			for (int i = 0; i < columns.size(); i++) {
				value = "";
				cell = row.getCell(i);
				if (cell != null) {
					// 注意：一定要设成这个，否则可能会出现乱码
					switch (cell.getCellType()) {
					case HSSFCell.CELL_TYPE_STRING:
						value = cell.getStringCellValue().trim();

						if (StringUtils.isNotBlank(value)) {
							try {
								if (TIME_FORMAT_1.matcher(value).matches() || TIME_FORMAT_2.matcher(value).matches()
										|| TIME_FORMAT_3.matcher(value).matches()
										|| TIME_FORMAT_4.matcher(value).matches()) {
									value = DateUtil.getInstance().parseDateStr(value, DateUtil.YYYY__MM__DD);
									break;
								}
							} catch (Exception e) {
								LOG.error("日期类型无法转换" + value);
							}
						}
						break;
					case HSSFCell.CELL_TYPE_NUMERIC:
						if (HSSFDateUtil.isCellDateFormatted(cell)) {
							date = cell.getDateCellValue();
							if (date != null) {
								value = DateUtil.getInstance().dateToStr(date, DateUtil.YYYY__MM__DD);
							} else {
								value = "";
							}
						} else {
							if(210000==table.getTableId()||210001==table.getTableId()||210002==table.getTableId()||210003==table.getTableId()){
								value = new DecimalFormat("#.########").format(cell.getNumericCellValue());
							}else{
								value = new DecimalFormat("#.####").format(cell.getNumericCellValue());
							}
							
						}
						break;
					case HSSFCell.CELL_TYPE_FORMULA:
						// 导入时如果为公式生成的数据则无值
						try {
							if (!cell.getStringCellValue().equals("")) {
								value = cell.getStringCellValue();
							} else {
								value = cell.getNumericCellValue() + "";
							}
						} catch (Exception e) {
							value = "";
							LOG.error(e.getMessage());
							break;
						}
						break;
					case HSSFCell.CELL_TYPE_BLANK:
						break;
					case HSSFCell.CELL_TYPE_ERROR:
						value = "";
						break;
					case HSSFCell.CELL_TYPE_BOOLEAN:
						value = (cell.getBooleanCellValue() == true ? "Y" : "N");
						break;
					default:
						value = "";
					}
				}
				// 特殊字符转化，'会引起sql拼接出现问题
				if (StringUtils.isNotBlank(value)) {
					value = value.replace("\\", "\\\\");
					if (value.contains("'")) {
						value = value.replace("'", "\\'");
					}
					if (value.contains("\"")) {
						value = value.replace("\"", "\\\"");
					}
				}
				map.put(columns.get(i).getMetricCode(), value.replaceAll(" ", ""));
				map.put("rowIndex", rowIndex + 1);
			}
			if(table.getTableId()==210001){
				String extraCflb=map.get("CF_CFLB")==null?"":map.get("CF_CFLB").toString();
	        	String extraQtlb=map.get("CF_QTLB")==null?"":map.get("CF_QTLB").toString();
	        	String A000001 = CleanData(map.get("A000001").toString()).replaceAll("\\(", "（").replaceAll("\\)", "）");
				map.put("A000001", A000001);
	        	String CF_WSH = CleanData(map.get("CF_WSH").toString()).replaceAll("\\[", "〔").replaceAll("\\]", "〕").
	        			replaceAll("【", "〔").replaceAll("】", "〕").
	        			replaceAll("﹝", "〔").replaceAll("﹞", "〕").
	        			replaceAll("［", "〔").replaceAll("］", "〕").
	        			replaceAll("\\{", "〔").replaceAll("\\}", "〕");
				map.put("CF_WSH", CF_WSH);
	        	extraCflb=extraCflb.replaceAll(",", ";");
	        	if(extraCflb.indexOf("其他")>-1&&StringUtils.isNotBlank(extraQtlb)){
	        		extraCflb=extraCflb+"-"+extraQtlb;
	        	}
	        	map.put("CF_CFLB", extraCflb);
			}else if(table.getTableId()==210003){
				String extraCflb=map.get("CF_CFLB")==null?"":map.get("CF_CFLB").toString();
	        	String extraQtlb=map.get("CF_QTLB")==null?"":map.get("CF_QTLB").toString();
	        	String B000001 = CleanData(map.get("B000001").toString()).replaceAll("\\(", "（").replaceAll("\\)", "）");
				map.put("B000001", B000001);
	        	String CF_WSH = CleanData(map.get("CF_WSH").toString()).replaceAll("\\[", "〔").replaceAll("\\]", "〕").
	        			replaceAll("【", "〔").replaceAll("】", "〕").
	        			replaceAll("﹝", "〔").replaceAll("﹞", "〕").
	        			replaceAll("［", "〔").replaceAll("］", "〕").
	        			replaceAll("\\{", "〔").replaceAll("\\}", "〕");
				map.put("CF_WSH", CF_WSH);
	        	extraCflb=extraCflb.replaceAll(",", ";");
	        	if(extraCflb.indexOf("其他")>-1&&StringUtils.isNotBlank(extraQtlb)){
	        		extraCflb=extraCflb+"-"+extraQtlb;
	        	}
	        	map.put("CF_CFLB", extraCflb);
			}else if(table.getTableId()==210002) {
				String extraCflb = map.get("XK_XKLB") == null ? "" : map.get("XK_XKLB").toString();
				String extraQtlb = map.get("XK_QTLB") == null ? "" : map.get("XK_QTLB").toString();
				String B000001 = CleanData(map.get("B000001").toString()).replaceAll("\\(", "（").replaceAll("\\)", "）");
				map.put("B000001", B000001);
				String XK_WSH = CleanData(map.get("XK_WSH").toString()).replaceAll("\\[", "〔").replaceAll("\\]", "〕").
	        			replaceAll("【", "〔").replaceAll("】", "〕").
	        			replaceAll("﹝", "〔").replaceAll("﹞", "〕").
	        			replaceAll("［", "〔").replaceAll("］", "〕").
	        			replaceAll("\\{", "〔").replaceAll("\\}", "〕");
				map.put("XK_WSH", XK_WSH);
				if (extraCflb.indexOf("其他") > -1 && StringUtils.isNotBlank(extraQtlb)) {
					extraCflb = extraCflb + "-" + extraQtlb;
				}
				map.put("XK_XKLB", extraCflb);
			}else if(table.getTableId()==210000) {
				String extraCflb = map.get("XK_XKLB") == null ? "" : map.get("XK_XKLB").toString();
				String extraQtlb = map.get("XK_QTLB") == null ? "" : map.get("XK_QTLB").toString();
				String A000001 = CleanData(map.get("A000001").toString()).replaceAll("\\(", "（").replaceAll("\\)", "）");
				map.put("A000001", A000001);
				String XK_WSH = CleanData(map.get("XK_WSH").toString()).replaceAll("\\[", "〔").replaceAll("\\]", "〕").
	        			replaceAll("【", "〔").replaceAll("】", "〕").
	        			replaceAll("﹝", "〔").replaceAll("﹞", "〕").
	        			replaceAll("［", "〔").replaceAll("］", "〕").
	        			replaceAll("\\{", "〔").replaceAll("\\}", "〕");
				map.put("XK_WSH", XK_WSH);
				if (extraCflb.indexOf("其他") > -1 && StringUtils.isNotBlank(extraQtlb)) {
					extraCflb = extraCflb + "-" + extraQtlb;
				}
				map.put("XK_XKLB", extraCflb);
			}
			/* 把数据添加到List里面 */
			listDataMap.add(map);
			LOG.debug(map.toString());
		}

		return listDataMap;

	}

	@Override
	public Map<String, Object> readExcelFileHead(Sheet st, String batchId, List<Column> columns) {
		Map<String, Object> map = null;
		try {
			Row row = null;
			// 获取校验头
			row = st.getRow(0);
			for (int i = 0; i < columns.size(); i++) {

				if (!row.getCell(i).toString().trim().equals(columns.get(i).getMetricName())) {
					LOG.debug("EXCEL第{}列表头:{}", i, row.getCell(i));
					LOG.debug("数据库配置 第{}列表头:{}", i, columns.get(i).getMetricName());
					LOG.info("模板表头已被更改,请用规定的模板上传数据。");
					map = new HashMap<String, Object>();
					map.put("ERROR_MESS", "模板表头已被更改,请用规定的模板上传数据。"+columns.get(i).getMetricName()+"---"+row.getCell(i));
					break;
				}

			}
			return map;

		} catch (EncryptedDocumentException e) {
			map = new HashMap<String, Object>();
			map.put("ERROR_MESS", "模板保护密码已被更改");

		} catch (Exception e) {
			map = new HashMap<String, Object>();
			map.put("ERROR_MESS", "模板表头已被更改,请用规定的模板上传数据。");
		}
		return map;
	}

	@Override
	public Map<String, String> checkHeader(Sheet sheet, String[] standard) {
		Map<String, String> map = null;
		try {
			Row row = null;
			// 获取校验头
			row = sheet.getRow(0);
			for (int i = 0; i < standard.length; i++) {

				if (!row.getCell(i).toString().trim().equals(standard[i])) {
					LOG.debug("EXCEL第{}列表头:{}", i, row.getCell(i));
					LOG.debug("标准规定 第{}列表头:{}", i, standard[i]);
					LOG.info("模板表头不符合前端传入规范");
					map = new HashMap<String, String>();
					map.put("ERROR_MESS", "模板表头不符合前端传入规范");
					break;
				}

			}
			return map;

		} catch (EncryptedDocumentException e) {
			map = new HashMap<String, String>();
			map.put("ERROR_MESS", "模板保护密码已被更改");

		} catch (Exception e) {
			map = new HashMap<String, String>();
			map.put("ERROR_MESS", "模板表头不符合前端传入规范");
		}
		return map;

	}

	@Override
	public List<Map<String, Object>> readExcelData(int start, int end, Sheet st, String[] column) {

		Row row = null;
		int length = 0;
		Map<String, Object> map = null;
		Cell cell = null;
		String value = null;
		length = st.getLastRowNum();

		if (end > length) {
			end = length;
		}
		List<Map<String, Object>> listDataMap = new LinkedList<>();
		for (int rowIndex = start + 1; rowIndex <= end; rowIndex++) {
			row = st.getRow(rowIndex);
			if (row == null) {
				continue;
			}

			short lastCellNum = row.getLastCellNum();

			boolean emptyRow = true;

			for (int cellIndex = 0; cellIndex <= lastCellNum; cellIndex++) {

				if (row.getCell(cellIndex, Row.RETURN_BLANK_AS_NULL) != null) {

					row.getCell(cellIndex).setCellType(HSSFCell.CELL_TYPE_STRING);

					if (!"".equals(row.getCell(cellIndex).getStringCellValue().trim())) {
						{

							emptyRow = false;
							break;
						}
					}

				}
			}

			if (emptyRow) {

				continue;
			}
			map = new HashMap<String, Object>();
			for (int i = 0; i < lastCellNum; i++) {
				value = "";
				cell = row.getCell(i);
				if (cell != null) {
					// 注意：一定要设成这个，否则可能会出现乱码
					switch (cell.getCellType()) {
					case HSSFCell.CELL_TYPE_STRING:
						value = cell.getStringCellValue().trim();
						break;
					case HSSFCell.CELL_TYPE_BLANK:
						break;

					default:
						value = "";
					}
				}
				map.put(column[i], value);
				map.put("rowIndex", rowIndex + 1);
			}
			/* 把数据添加到List里面 */
			listDataMap.add(map);
			LOG.debug(map.toString());
		}

		return listDataMap;
	}
	
	
	public static String CleanData(String data) {
		String repl = "";
		if(StringUtils.isNotBlank(data)) {
			Pattern p = Pattern.compile("\\s*|\t|\r|\n");
            Matcher m = p.matcher(data);
            repl = m.replaceAll("").replaceAll("[?]", "").replaceAll("\\?", "");
            Matcher matcher = Pattern.compile("[a-z]").matcher(repl);
            while(matcher.find()){
            	repl = repl.replace(matcher.group(), matcher.group().toUpperCase());
            }
          
		}
		return repl;
		
	}
	
}
